package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import model.InvalidValueException;
import model.User;
import utils.DateUtils;
import utils.Security;

/**
 *
 * @author fred
 */
public class UserDAO {
    
    private Connection con;
    
    public UserDAO(Connection con) {
        this.con = con;
    }
    
    public boolean insertUser(User user) {
        try {
            // Hash the user password
            String hashedPassword = Security.hashString(user.getPassword());
            user.setPassword(hashedPassword);
            
            con.command("INSERT INTO USERS(user_login, user_password, user_name, user_email, user_birthDate) "
                    + "VALUES('" + user.getLogin() + "',"
                        + "'" + user.getPassword() + "',"
                        + "'" + user.getName() + "',"
                        + "'" + user.getEmail() + "',"
                        + "'" + DateUtils.formatDateTime(user.getBirthDate()) + "')");
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }
    
    public boolean editUser(User user) {
        try {
            con.command("UPDATE USERS SET user_password = '" + user.getPassword() + "',"
                        + "user_name = '" + user.getName() + "',"
                        + "user_email = '" + user.getEmail() + "',"
                        + "user_birthDate = '" + DateUtils.formatDateTime(user.getBirthDate()) + "' "
                    + "WHERE user_login = '" + user.getLogin() + "'");
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }
    
    public boolean deleteUser(User user) {
        try {
            con.command("DELETE FROM USERS "
                    + "WHERE user_login = '" + user.getLogin() + "'");
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }
    
    public boolean followUser(User user, User followed) {
        try {
            con.command("INSERT INTO FOLLOWS(follower_user_login, followed_user_login) "
                    + "VALUES ('" + user.getLogin() + "', '" + followed.getLogin() + "')");
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }
    
    public boolean unfollowUser(User user, User followed) {
        try {
            con.command("DELETE FROM FOLLOWS "
                    + "WHERE follower_user_login = '" + user.getLogin() + "' "
                        + "AND followed_user_login = '" + followed.getLogin() + "'");
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }
    
    public User getUser(String login) {
        try {
            ResultSet rs = con.query("SELECT USERS.user_password,"
                        + "USERS.user_name,"
                        + "USERS.user_email,"
                        + "USERS.user_birthDate "
                    + "FROM USERS "
                    + "WHERE USERS.user_login = '" + login + "'");
            if (rs.next()) {
                String password     = rs.getString("user_password");
                String name         = rs.getString("user_name");
                String email        = rs.getString("user_email");
                Date birthDate      = rs.getDate("user_birthDate");
                return new User(login, password, name, email, birthDate);
            }
        } catch (InvalidValueException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }
    
    public ArrayList<User> getUsers(String name) {
        ArrayList<User> users = new ArrayList<User>();
        try {
            ResultSet rs = con.query("SELECT USERS.user_login, "
                        + "USERS.user_password,"
                        + "USERS.user_name,"
                        + "USERS.user_email,"
                        + "USERS.user_birthDate "
                    + "FROM USERS "
                    + "WHERE USERS.user_login LIKE '%" + name + "%' "
                        + "OR USERS.user_name LIKE '%" + name + "%'");
            while (rs.next()) {
                String login        = rs.getString("user_login");
                String password     = rs.getString("user_password");
                String nameA        = rs.getString("user_name");
                String email        = rs.getString("user_email");
                Date birthDate      = rs.getDate("user_birthDate");
                users.add(new User(login, password, nameA, email, birthDate));
            }
        } catch (InvalidValueException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return users;
    }
    
    public ArrayList<User> getUsers() {
        ArrayList<User> users = new ArrayList<User>();
        try {
            ResultSet rs = con.query("SELECT USERS.user_login,"
                        + "USERS.user_password,"
                        + "USERS.user_name,"
                        + "USERS.user_email,"
                        + "USERS.user_birthDate "
                    + "FROM USERS");
            while (rs.next()) {
                String login        = rs.getString("user_login");
                String password     = rs.getString("user_password");
                String name         = rs.getString("user_name");
                String email        = rs.getString("user_email");
                Date birthDate      = rs.getDate("user_birthDate");
                users.add(new User(login, password, name, email, birthDate));
            }
        } catch (InvalidValueException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return users;
    }
    
    public ArrayList<User> getUserFolloweds(User user) {
        ArrayList<User> users = new ArrayList<User>();
        try {
            ResultSet rs = con.query("SELECT USERS.user_login,"
                        + "USERS.user_password,"
                        + "USERS.user_name,"
                        + "USERS.user_email,"
                        + "USERS.user_birthDate "
                    + "FROM USERS "
                    + "INNER JOIN FOLLOWS ON FOLLOWS.followed_user_login = USERS.user_login "
                    + "WHERE FOLLOWS.follower_user_login = '" + user.getLogin() + "'");
            while (rs.next()) {
                String login        = rs.getString("user_login");
                String password     = rs.getString("user_password");
                String name         = rs.getString("user_name");
                String email        = rs.getString("user_email");
                Date birthDate      = rs.getDate("user_birthDate");
                users.add(new User(login, password, name, email, birthDate));
            }
        } catch (InvalidValueException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return users;
    }
    
    public ArrayList<User> getUserFollowers(User user) {
        ArrayList<User> users = new ArrayList<User>();
        try {
            ResultSet rs = con.query("SELECT USERS.user_login,"
                        + "USERS.user_password,"
                        + "USERS.user_name,"
                        + "USERS.user_email,"
                        + "USERS.user_birthDate "
                    + "FROM USERS "
                    + "INNER JOIN FOLLOWS ON FOLLOWS.follower_user_login = USERS.user_login "
                    + "WHERE FOLLOWS.followed_user_login = '" + user.getLogin() + "'");
            while (rs.next()) {
                String login        = rs.getString("user_login");
                String password     = rs.getString("user_password");
                String name         = rs.getString("user_name");
                String email        = rs.getString("user_email");
                Date birthDate      = rs.getDate("user_birthDate");
                users.add(new User(login, password, name, email, birthDate));
            }
        } catch (InvalidValueException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return users;
    }
    
    public int getUserFollowersCount(User user) {
        try {
            ResultSet rs = con.query("SELECT COUNT(USERS.user_login) "
                    + "FROM USERS "
                    + "INNER JOIN FOLLOWS ON FOLLOWS.follower_user_login = USERS.user_login "
                    + "WHERE FOLLOWS.followed_user_login = '" + user.getLogin() + "'");
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }
    
    public boolean isUserFollower(User user, User followed) {
        try {
            ResultSet rs = con.query("SELECT COUNT(*) "
                    + "FROM FOLLOWS "
                    + "WHERE FOLLOWS.followed_user_login = '" + followed.getLogin() + "' "
                        + "AND FOLLOWS.follower_user_login = '" + user.getLogin() + "'");
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }
    
    // ---------------------------------------------------------------------- //
    
    public ArrayList<User> getMostPopularUsers(int count) {
        ArrayList<User> users = new ArrayList<User>();
        try {
            ResultSet rs = con.query("SELECT USERS.user_login, "
                        + "USERS.user_password,"
                        + "USERS.user_name,"
                        + "USERS.user_email,"
                        + "USERS.user_birthDate "
                    + "FROM USERS "
                    + "LEFT OUTER JOIN FOLLOWS ON FOLLOWS.followed_user_login = USERS.user_login "
                    + "GROUP BY USERS.user_login "
                    + "ORDER BY COUNT(FOLLOWS.follower_user_login) DESC "
                    + "LIMIT 0, " + count);
            while (rs.next()) {
                String login        = rs.getString("user_login");
                String password     = rs.getString("user_password");
                String name         = rs.getString("user_name");
                String email        = rs.getString("user_email");
                Date birthDate      = rs.getDate("user_birthDate");
                users.add(new User(login, password, name, email, birthDate));
            }
        } catch (InvalidValueException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return users;
    }
    
}
